//
//  HydantDAO.m
//  MapProject
//

//

#import "HydrantDAO.h"
#import "sqlite3.h"
#import "HydrantData.h"
#import "FoundationAdditions.h"

#define UPDATE_HYDRANT @"UPDATE HYDRANT SET ADDRESS='%@',GPM=%@,REMARKS='%@',LAT=%@,LON=%@ where HYDRANT_ID=%@;"
#define INSERT_HYDRANT @"INSERT into HYDRANT (HYDRANT_ID, ADDRESS,GPM,REMARKS,LAT,LON) VALUES (%@,'%@',%@,'%@', %@, %@);"
#define SELECT_HYDRANTS @"select * from HYDRANT"

@implementation HydrantDAO

//
//This method returns hydrant map data retrieved from SQLite database
//

-(NSArray *)getMapData{
	
	NSString *path = [[NSString alloc]initWithString:NSUserDocumentDirectory()];
	sqlite3 *database;
	// Open a connection to the database. 
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) 
	{      
		//TRUE
	} 
	else 
	{
		//close connection to release resources.
		sqlite3_close(database);
		NSAssert1(0, @"Failed to open connection '%s'.", sqlite3_errmsg(database));
	
	}
	
	NSMutableArray* hydrantsArray;
	
	hydrantsArray=[[NSMutableArray alloc]init];
	
	sqlite3_stmt *select_statement=nil;
	
	NSString *sql =[NSString stringWithFormat:SELECT_HYDRANTS];
	if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	
	int columncount=sqlite3_column_count(select_statement);
	
	NSMutableDictionary* dic;
	
	while (sqlite3_step(select_statement) == SQLITE_ROW) 
	{   
		dic=[[NSMutableDictionary alloc]init];
		
		for(int j=0;j<columncount;j++)
		{
			[dic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
		}
		
		[hydrantsArray addObject:dic];
		[dic release];      
	}
	[hydrantsArray retain];
	
	sqlite3_finalize(select_statement);
	
	sqlite3_close(database);
	[path release];
	return [hydrantsArray autorelease];
	
}
//
//This method adds the HydrantData members to the SQLite database
//

-(void)addHydrantData:(HydrantData*)hydrantData{
	
	NSString *path = [[NSString alloc]initWithString:NSUserDocumentDirectory()];
	NSMutableArray* hydrantArray;
	
	sqlite3 *database;
	// Open a connection to the database.
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) 
	{      
		//TRUE
	} 
	else 
	{
		//Close to free up resources.
		sqlite3_close(database);
		NSAssert1(0, @"Failed to open database connection'%s'.", sqlite3_errmsg(database));
	}
	
	
	
	hydrantArray=[[NSMutableArray alloc]init];
	
	sqlite3_stmt *select_statement=nil;
	
	NSString *sql =[NSString stringWithFormat:@"select ALL HYDRANT_ID from HYDRANT"];
	if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"prepare statement failure '%s'.", sqlite3_errmsg(database));
	}
	
	NSMutableDictionary* dic;
	int columncount=sqlite3_column_count(select_statement);
	
	while (sqlite3_step(select_statement) == SQLITE_ROW) 
	{   
		dic=[[NSMutableDictionary alloc]init];
		
		for(int j=0;j<columncount;j++)
		{
			[dic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
		}
		
		[hydrantArray addObject:dic];
		[dic release];
		
	}
	
	hydrantData.hydrantId=[[NSNumber alloc]initWithInt:[hydrantArray count]+1000];
	sqlite3_stmt *insert_statement=nil;
	
	NSString *statement = [[NSString alloc] initWithFormat:INSERT_HYDRANT,
						   hydrantData.hydrantId,hydrantData.address,hydrantData.gpm, hydrantData.remarks,hydrantData.lat,hydrantData.lon];
	NSLog(@"Test: %@", statement);
	
	if (sqlite3_exec(database, [statement UTF8String],NULL,&insert_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"prepare statement failure  '%s'.", sqlite3_errmsg(database));
		
	}
	sqlite3_finalize(select_statement);
	sqlite3_finalize(insert_statement);
	[statement release];
	[hydrantArray autorelease];
	[path release];
	sqlite3_close(database);
	
}
//
//This method updates the SQLite database with the HydrantData object
//
-(void)updateHydrantData:(HydrantData*)hydrantData{
	
	NSString *path = [[NSString alloc]initWithString:NSUserDocumentDirectory()];
	
	sqlite3 *database;
	//sqlite3_stmt *statement=nil;
	// Open the database. The database was prepared outside the application.
	if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) 
	{      
		//TRUE
	} 
	else 
	{
		//close to free up resources.
		sqlite3_close(database);
		NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
		
	}
	NSString *statement = [[NSString alloc] initWithFormat:UPDATE_HYDRANT,
						   hydrantData.address,hydrantData.gpm,hydrantData.remarks, hydrantData.lat,hydrantData.lon,hydrantData.hydrantId];
	NSLog(@"Test: %@", statement);
	sqlite3_stmt *update_statement=nil;
	
	if (sqlite3_exec (database, [statement UTF8String], NULL,&update_statement, NULL) != SQLITE_OK) {
		NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
	}
	sqlite3_finalize(update_statement);
	[statement release];
	[path release];
	sqlite3_close(database);
}
@end
